﻿using CommonModel;
using Dapper;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace DBTools
{
    /// <summary>
    /// 数据库访问
    /// </summary>
    public class DapperContext
    {
        public DapperContext(string dbName, string connstr)
        {
            ConnectionString = connstr;
            DBName = dbName;
        }
        #region 数据库设置
        /// <summary>
        /// 数据库连接串
        /// </summary>
        public string ConnectionString { get; set; }
        public string DBName { get; set; }
        //命令超时时间
        public int? CommandTimeOut { get; set; }
        #endregion

        #region 私有变量

        private bool needclose = false;
        public IDbConnection Connection
        {
            get; set;
        }
        public IDbTransaction Transaction { get; set; }

        #endregion


        #region 打开关闭数据库
        /// <summary>
        /// 打开数据库
        /// 先判断，是否指定了使用主库，然后判断是否指定了从库
        /// 然后判断本次是否需要写操作
        /// 最后判断是否有从库，如果没有从库，则用主库代替
        /// </summary>
        /// <param name="write"></param>
        /// <returns></returns>
        public IDbConnection OpenDB()
        {
            if (Connection != null && Connection.State != ConnectionState.Closed)
            {
                return Connection;
            }
            Connection = new MySqlConnection(ConnectionString);

            Connection.Open();
            return Connection;
        }

        private IDbConnection GetOpenConnection(out bool needclose)
        {
            if (Connection != null && Connection.State != ConnectionState.Closed)
            {
                needclose = false;
                return Connection;
            }
            needclose = true;
            return OpenDB();
        }
        public void CloseDB()
        {
            if (Connection != null && Connection.State != ConnectionState.Closed)
            {
                Transaction = null;
                Connection.Close();
            }
        }
        #endregion

        #region 事务

        /// <summary>
        /// 启用事务
        /// </summary>
        /// <returns></returns>
        public DapperContext BeginTransaction()
        {

            if (Connection != null && Connection.State != ConnectionState.Closed)
            {
                throw new Exception("使用事务时，应打开新连接。");
            }
            Transaction = OpenDB().BeginTransaction();
            return this;
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        /// <returns></returns>
        public DapperContext Commit()
        {
            if (Transaction != null)
            {
                Transaction.Commit();
            }
            return this;
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        /// <returns></returns>
        public DapperContext Rollback()
        {
            if (Transaction != null)
            {
                Transaction.Rollback();
            }
            return this;
        }
        #endregion

        #region 异步操作
        #region 查询

        public async Task<List<dynamic>> QueryAsync(string sql, object query = null)
        {
            try
            {
                var m = await GetOpenConnection(out needclose).QueryAsync(sql, query, commandTimeout: CommandTimeOut);
                return m.ToList();
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }

        public async Task<List<T>> QueryAsync<T>(string sql, object query = null)
        {
            try
            {
                var m = await GetOpenConnection(out needclose).QueryAsync<T>(sql, query, commandTimeout: CommandTimeOut);
                return m.ToList();
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        public async Task<dynamic> SingleAsync(string sql, object query = null)
        {
            try
            {
                var m = await GetOpenConnection(out needclose).QueryAsync(sql, query, commandTimeout: CommandTimeOut);
                return m.FirstOrDefault();
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }

        public async Task<T> SingleAsync<T>(string sql, object query = null)
        {
            try
            {
                var m = await GetOpenConnection(out needclose).QueryAsync<T>(sql, query, commandTimeout: CommandTimeOut);
                return m.FirstOrDefault();
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        public async Task<T> ExecuteScalarAsync<T>(string sql, object query = null)
        {

            try
            {
                var m = await GetOpenConnection(out needclose).ExecuteScalarAsync<T>(sql, query, commandTimeout: CommandTimeOut);
                return m;
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }

        #endregion

        #region 执行sql
        /// <summary>
        /// 执行sql，返回受影响记录数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="model"></param>
        /// <param name="tran"></param>
        public async Task<int> ExecuteAsync(string sql, object model = null)
        {
            try
            {
                return await GetOpenConnection(out needclose).ExecuteAsync(sql, model, commandTimeout: CommandTimeOut);
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 执行sql，返回受影响条记录数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public async Task<int> ExecuteAsync(string sql, ParamList pl)
        {
            try
            {
                return await GetOpenConnection(out needclose).ExecuteAsync(sql, pl.GetParameters(), commandTimeout: CommandTimeOut);
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }


        /// <summary>
        /// 同一个sql语句，批量操作多个对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="models"></param>
        /// <param name="tran"></param>
        public async Task<int> ExecuteAsync(string sql, IEnumerable<object> models)
        {
            try
            {
                return await GetOpenConnection(out needclose).ExecuteAsync(sql, models, commandTimeout: CommandTimeOut);
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }

        /// <summary>
        /// 批量执行多个语句
        /// </summary>
        /// <param name="sqllist"></param>
        /// <param name="pl"></param>
        /// <returns></returns>
        public async Task ExecuteAsync(List<string> sqllist, object model = null)
        {
            try
            {
                GetOpenConnection(out needclose);
                foreach (var sql in sqllist)
                {
                    await Connection.ExecuteAsync(sql, model, commandTimeout: CommandTimeOut);
                }
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 批量执行sql语句
        /// </summary>
        /// <param name="sqllist"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        public async Task ExecuteAsync(List<string> sqllist, ParamList pl)
        {
            try
            {
                GetOpenConnection(out needclose);
                foreach (var sql in sqllist)
                {
                    await Connection.ExecuteAsync(sql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                }
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        #endregion

        #region Result

        /// <summary>
        /// 返回一个ResultSet  查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public async Task<ResultSet<T>> GetResultSetAsync<T>(string sql, ParamList pl)
        {
            try
            {
                ResultSet<T> rs = null;

                GetOpenConnection(out needclose);
                if (pl.ispage)
                {
                    string pagesql = GetPageSql(sql, pl);
                    string countsql = GetCountSql(sql);
                    rs = await GetResultSetAsync<T>(pagesql, countsql, pl);
                }
                else
                {
                    var m = await Connection.QueryAsync<T>(sql + (string.IsNullOrWhiteSpace(pl.orderby) ? "" : " order by " + pl.orderby), pl.GetParameters(), commandTimeout: CommandTimeOut);
                    rs = new ResultSet<T>() { Value = m.ToList(), Total = m.Count() };
                }
                if (rs.Total > 0 && (!string.IsNullOrWhiteSpace(pl.TotalColumns) || !string.IsNullOrWhiteSpace(pl.TotalAvgColumns)
                    || !string.IsNullOrWhiteSpace(pl.PageTotalColumns) || !string.IsNullOrWhiteSpace(pl.PageAvgColumns)))
                {
                    rs.Footer = new List<IDictionary<string, object>>();
                    if (!string.IsNullOrWhiteSpace(pl.PageAvgColumns))
                    {
                        var ft = await Connection.QueryAsync(GetTotalSql(GetPageSql(sql, pl), pl.PageAvgColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.TotalAvgColumns))
                    {
                        var ft = await Connection.QueryAsync(GetTotalSql(sql, pl.TotalAvgColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.PageTotalColumns))
                    {
                        var ft = await Connection.QueryAsync(GetTotalSql(GetPageSql(sql, pl), pl.PageTotalColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.TotalColumns))
                    {
                        var ft = await Connection.QueryAsync(GetTotalSql(sql, pl.TotalColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                }


                return rs;

            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 指定查询语句和查询总记录数语句，用于查询性能优化时调用
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageSql"></param>
        /// <param name="countSql"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public async Task<ResultSet<T>> GetResultSetAsync<T>(string pageSql, string countSql, ParamList pl)
        {

            try
            {
                GetOpenConnection(out needclose);
                if (pl.ispage)
                {
                    var m = await Connection.QueryAsync<T>(pageSql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    var c = await Connection.ExecuteScalarAsync<int>(countSql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    return new ResultSet<T>() { Value = m.ToList(), Total = c };
                }
                else
                {
                    throw new Exception("本方法仅支持分页查询！");
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 返回一个ResultSet  查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="OrderBy"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public async Task<ResultSet> GetResultSetAsync(string sql, ParamList pl)
        {
            try
            {
                ResultSet rs = null;
                GetOpenConnection(out needclose);
                if (pl.ispage)
                {
                    string pagesql = GetPageSql(sql, pl);
                    string countsql = GetCountSql(sql);

                    var m = await Connection.QueryAsync(pagesql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    var c = await Connection.ExecuteScalarAsync<int>(countsql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    rs = ResultSet.GetResultSet(m, c);
                }
                else
                {
                    var m = await Connection.QueryAsync(sql + (string.IsNullOrWhiteSpace(pl.orderby) ? "" : " order by " + pl.orderby), pl.GetParameters(), commandTimeout: CommandTimeOut);
                    rs = ResultSet.GetResultSet(m);
                }
                if (rs.Total > 0 && (!string.IsNullOrWhiteSpace(pl.TotalColumns) || !string.IsNullOrWhiteSpace(pl.TotalAvgColumns)
             || !string.IsNullOrWhiteSpace(pl.PageTotalColumns) || !string.IsNullOrWhiteSpace(pl.PageAvgColumns)))
                {
                    rs.Footer = new List<IDictionary<string, object>>();
                    if (!string.IsNullOrWhiteSpace(pl.PageAvgColumns))
                    {
                        var ft = await Connection.QueryAsync(GetTotalSql(GetPageSql(sql, pl), pl.PageAvgColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.TotalAvgColumns))
                    {
                        var ft = await Connection.QueryAsync(GetTotalSql(sql, pl.TotalAvgColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.PageTotalColumns))
                    {
                        var ft = await Connection.QueryAsync(GetTotalSql(GetPageSql(sql, pl), pl.PageTotalColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.TotalColumns))
                    {
                        var ft = await Connection.QueryAsync(GetTotalSql(sql, pl.TotalColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                }


                return rs;
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
 
        /// <summary>
        /// 指定查询语句和查询总记录数语句，用于查询性能优化时调用
        /// </summary>
        /// <param name="pageSql"></param>
        /// <param name="countSql"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public async Task<ResultSet> GetResultSetAsync(string pageSql, string countSql, ParamList pl)
        {

            try
            {
                GetOpenConnection(out needclose);
                if (pl.ispage)
                {
                    var m = await Connection.QueryAsync(pageSql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    var c = await Connection.ExecuteScalarAsync<int>(countSql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    return ResultSet.GetResultSet(m, c);
                }
                else
                {
                    throw new Exception("本方法仅支持分页查询！");
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        #endregion
        #endregion

        #region 同步操作
        #region 查询

        public List<dynamic> Query(string sql, object query = null)
        {
            try
            {
                var m = GetOpenConnection(out needclose).Query(sql, query, commandTimeout: CommandTimeOut);
                return m.ToList();
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }

        public List<T> Query<T>(string sql, object query = null)
        {
            try
            {
                var m = GetOpenConnection(out needclose).Query<T>(sql, query, commandTimeout: CommandTimeOut);
                return m.ToList();
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }

        public T ExecuteScalar<T>(string sql, object query = null)
        {

            try
            {
                var m = GetOpenConnection(out needclose).ExecuteScalar<T>(sql, query, commandTimeout: CommandTimeOut);
                return m;
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        public T Single<T>(string sql, object query = null)
        {

            try
            {
                var m = GetOpenConnection(out needclose).Query<T>(sql, query, commandTimeout: CommandTimeOut).FirstOrDefault();
                return m;
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        public dynamic Single(string sql, object query = null)
        {
            try
            {
                var m = GetOpenConnection(out needclose).Query(sql, query, commandTimeout: CommandTimeOut).FirstOrDefault();
                return m.ToList();
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        #endregion

        #region 执行sql
        /// <summary>
        /// 执行sql，返回受影响记录数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="model"></param>
        /// <param name="tran"></param>
        public int Execute(string sql, object model = null)
        {
            try
            {
                return GetOpenConnection(out needclose).Execute(sql, model, commandTimeout: CommandTimeOut);
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 执行sql，返回受影响条记录数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public int Execute(string sql, ParamList pl)
        {
            try
            {
                return GetOpenConnection(out needclose).Execute(sql, pl.GetParameters(), commandTimeout: CommandTimeOut);
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }


        /// <summary>
        /// 同一个sql语句，批量操作多个对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="models"></param>
        /// <param name="tran"></param>
        public int Execute(string sql, IEnumerable<object> models)
        {
            try
            {
                return GetOpenConnection(out needclose).Execute(sql, models, commandTimeout: CommandTimeOut);
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }

        /// <summary>
        /// 批量执行多个语句
        /// </summary>
        /// <param name="sqllist"></param>
        /// <param name="pl"></param>
        /// <returns></returns>
        public void Execute(List<string> sqllist, object model = null)
        {
            try
            {
                GetOpenConnection(out needclose);
                foreach (var sql in sqllist)
                {
                    Connection.Execute(sql, model, commandTimeout: CommandTimeOut);
                }
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 批量执行sql语句
        /// </summary>
        /// <param name="sqllist"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        public void Execute(List<string> sqllist, ParamList pl)
        {
            try
            {
                GetOpenConnection(out needclose);
                foreach (var sql in sqllist)
                {
                    Connection.Execute(sql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                }
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        #endregion

        #region Result

        /// <summary>
        /// 返回一个ResultSet  查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public ResultSet<T> GetResultSet<T>(string sql, ParamList pl)
        {
            try
            {
                ResultSet<T> rs = null;

                GetOpenConnection(out needclose);
                if (pl.ispage)
                {
                    string pagesql = GetPageSql(sql, pl);
                    string countsql = GetCountSql(sql);
                    rs = GetResultSet<T>(pagesql, countsql, pl);
                }
                else
                {
                    var m = Connection.Query<T>(sql + (string.IsNullOrWhiteSpace(pl.orderby) ? "" : " order by " + pl.orderby), pl.GetParameters(), commandTimeout: CommandTimeOut);
                    rs = new ResultSet<T>() { Value = m.ToList(), Total = m.Count() };
                }
                if (rs.Total > 0 && (!string.IsNullOrWhiteSpace(pl.TotalColumns) || !string.IsNullOrWhiteSpace(pl.TotalAvgColumns)
                    || !string.IsNullOrWhiteSpace(pl.PageTotalColumns) || !string.IsNullOrWhiteSpace(pl.PageAvgColumns)))
                {
                    rs.Footer = new List<IDictionary<string, object>>();
                    if (!string.IsNullOrWhiteSpace(pl.PageAvgColumns))
                    {
                        var ft =  Connection.Query(GetTotalSql(GetPageSql(sql, pl), pl.PageAvgColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.TotalAvgColumns))
                    {
                        var ft =  Connection.Query(GetTotalSql(sql, pl.TotalAvgColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.PageTotalColumns))
                    {
                        var ft =  Connection.Query(GetTotalSql(GetPageSql(sql, pl), pl.PageTotalColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.TotalColumns))
                    {
                        var ft =  Connection.Query(GetTotalSql(sql, pl.TotalColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                }


                return rs;
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 指定查询语句和查询总记录数语句，用于查询性能优化时调用
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageSql"></param>
        /// <param name="countSql"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public ResultSet<T> GetResultSet<T>(string pageSql, string countSql, ParamList pl)
        {

            try
            {
                GetOpenConnection(out needclose);
                if (pl.ispage)
                {
                    var m = Connection.Query<T>(pageSql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    var c = Connection.ExecuteScalar<int>(countSql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    return new ResultSet<T>() { Value = m.ToList(), Total = c };
                }
                else
                {
                    throw new Exception("本方法仅支持分页查询！");
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        /// <summary>
        /// 返回一个ResultSet  查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="OrderBy"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public ResultSet GetResultSet(string sql, ParamList pl)
        {
            try
            {
                ResultSet rs = null;
                GetOpenConnection(out needclose);
                if (pl.ispage)
                {
                    string pagesql = GetPageSql(sql, pl);
                    string countsql = GetCountSql(sql);

                    var m = Connection.Query(pagesql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    var c = Connection.ExecuteScalar<int>(countsql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    rs = ResultSet.GetResultSet(m, c);
                }
                else
                {
                    var m = Connection.Query(sql + (string.IsNullOrWhiteSpace(pl.orderby) ? "" : " order by " + pl.orderby), pl.GetParameters(), commandTimeout: CommandTimeOut);
                    rs = ResultSet.GetResultSet(m);
                }
                if (rs.Total > 0 && (!string.IsNullOrWhiteSpace(pl.TotalColumns) || !string.IsNullOrWhiteSpace(pl.TotalAvgColumns)
             || !string.IsNullOrWhiteSpace(pl.PageTotalColumns) || !string.IsNullOrWhiteSpace(pl.PageAvgColumns)))
                {
                    rs.Footer = new List<IDictionary<string, object>>();
                    if (!string.IsNullOrWhiteSpace(pl.PageAvgColumns))
                    {
                        var ft = Connection.Query(GetTotalSql(GetPageSql( sql,pl), pl.PageAvgColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.TotalAvgColumns))
                    {
                        var ft = Connection.Query(GetTotalSql(sql, pl.TotalAvgColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.PageTotalColumns))
                    {
                        var ft = Connection.Query(GetTotalSql(GetPageSql(sql, pl), pl.PageTotalColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                    if (!string.IsNullOrWhiteSpace(pl.TotalColumns))
                    {
                        var ft = Connection.Query(GetTotalSql(sql, pl.TotalColumns), pl.GetParameters());
                        var query = from m in ft select ResultSet.MapToDictionary((IDictionary<string, object>)m);
                        rs.Footer.AddRange(query);
                    }
                }
                return rs;
            }
            catch { throw; }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }

        /// <summary>
        /// 指定查询语句和查询总记录数语句，用于查询性能优化时调用
        /// </summary>
        /// <param name="pageSql"></param>
        /// <param name="countSql"></param>
        /// <param name="pl"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public ResultSet GetResultSet(string pageSql, string countSql, ParamList pl)
        {

            try
            {
                GetOpenConnection(out needclose);
                if (pl.ispage)
                {
                    var m = Connection.Query(pageSql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    var c = Connection.ExecuteScalar<int>(countSql, pl.GetParameters(), commandTimeout: CommandTimeOut);
                    return ResultSet.GetResultSet(m, c);
                }
                else
                {
                    throw new Exception("本方法仅支持分页查询！");
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (needclose)
                {
                    CloseDB();
                }
            }
        }
        #endregion

        #endregion

        #region 分页语句拼接
        private string GetPageSql(string sql, ParamList pl)
        {
            if (pl.ispage)
            {
                if (string.IsNullOrWhiteSpace(pl.orderby))
                {
                    return sql + " limit @start,@rows";
                }
                else
                {
                    return sql + " order by " + pl.orderby + " limit @start,@rows";
                }
            }
            else
            {
                if (!string.IsNullOrWhiteSpace(pl.orderby))
                {
                    return sql + " order by " + pl.orderby;
                }
                else
                {
                    return sql;
                }
            }

        }
        private string GetCountSql(string sql)
        {
            return "select count(1) from (" + sql + ") tab_base";
        }
        #endregion


        #region 汇总语句拼接
        /// <summary>
        /// 汇总语句拼接
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        private string GetTotalSql(string sql, string columns)
        {
            return "select " + columns + " from (" + sql + ") tab_base";
        }
        #endregion

    }
}
